using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using WeFrame.Core.Common;
using WeFrame.Core.DBUtility;
using WeFrame.Core.Model;
using WeFrame.Test.Model;
namespace WeFrame.Test.DAL
{
    /// <summary>
    /// 数据访问类:测试学生表
    /// </summary>
    ///
    public class TestStudentDAL
    {
        public TestStudentDAL()
        {
        }

        #region 基本方法=============================

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from  test_student");
            strSql.Append(" where id=@id ");
            MySqlParameter[] parameters = {
                 new MySqlParameter("@id", MySqlDbType.Int32,4)};
            parameters[0].Value = id;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// [增加]一条数据
        /// </summary>
        /// <param Name="model">数据对象</param>
        /// <returns>插入后数据的流水号 0 表示插入失败</returns>
        public  int Add(TestStudentEntity model)
        {
             string sql = @"INSERT INTO test_student
                    (
                     Name, Age, CreateBy, CreateTime, ModifyBy, ModifyTime, Remark
                    )
                    VALUES
                    (
                    @Name,@Age,@CreateBy,@CreateTime,@ModifyBy,@ModifyTime,@Remark
                    );select @@IDENTITY";
             MySqlParameter[] parameters = {
                 new MySqlParameter("@Name",model.Name),
                 new MySqlParameter("@Age",model.Age),
                 new MySqlParameter("@CreateBy",model.CreateBy),
                 new MySqlParameter("@CreateTime",model.CreateTime),
                 new MySqlParameter("@ModifyBy",model.ModifyBy),
                 new MySqlParameter("@ModifyTime",model.ModifyTime),
                 new MySqlParameter("@Remark",model.Remark)
             };
            object value = DbHelperSQL.GetSingle(sql, parameters);
            if (value != null)
            {
                return Int32.Parse(value.ToString());
            }
            return 0;
        }


        /// <summary>
        /// [查询]根据id获得对象
        /// </summary>
        /// <param Name="id">编号</param>
        /// <returns>实体对象</returns>
        public  TestStudentEntity GetModel(int id)
        {
             string sql = @"SELECT * FROM test_student WHERE Id=@Id";
             MySqlParameter[] parameters = {
                                new MySqlParameter("@Id",id)
                            };
             DataSet ds =DbHelperSQL.Query(sql, parameters);
             if (ds.Tables[0].Rows.Count > 0) 
             {
                 return DataRowToEntity(ds.Tables[0].Rows[0]);
             }
             return null;
        }




        /// <summary>
        /// 更新一个实体
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(TestStudentEntity model)
        {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("update test_student set ");
             strSql.Append("Name=@Name,Age=@Age,CreateBy=@CreateBy,CreateTime=@CreateTime,ModifyBy=@ModifyBy,ModifyTime=@ModifyTime,Remark=@Remark");
             strSql.Append(" where Id=@id");
             MySqlParameter[] parameters = {
                 new MySqlParameter("@Id",model.Id),
                 new MySqlParameter("@Name",model.Name),
                 new MySqlParameter("@Age",model.Age),
                 new MySqlParameter("@CreateBy",model.CreateBy),
                 new MySqlParameter("@CreateTime",model.CreateTime),
                 new MySqlParameter("@ModifyBy",model.ModifyBy),
                 new MySqlParameter("@ModifyTime",model.ModifyTime),
                 new MySqlParameter("@Remark",model.Remark)
             };
             int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
             if (rows > 0)
             {
                 return true;
             }
             else
             {
                 return false;
             }
        }
        /// <summary>
        /// 删除一条
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from test_student ");
            strSql.Append(" where Id=@id");
            MySqlParameter[] parameters = {
                 new MySqlParameter("@id", id)};
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion 基本方法=============================


         /// <summary>
         /// [查询]带分页多条件、多排序查询
         /// </summary>
         /// <param name="queryParamList">查询参数集合</param>
         /// <param name="queryOrderList">排序参数集合</param>
         /// <param name="queryPage">分页</param>
         /// <returns>数据集合</returns>
         public  DataSet QueryDataSet(List<QueryParam> queryParamList,List<QueryOrder> queryOrderList, QueryPage queryPage)
         {
             string where = " WHERE 1=1";
             string count ="";
             string sql = "";
             MySqlParameter[] parameters;
             //构造查询参数
             parameters = Utils.BuildSqlParameters(queryParamList);
             //构造查询条件
             where = Utils.BuildWhere(queryParamList);
             //查询对象总数
             count = "SELECT COUNT(*) FROM test_student "+ where;
             queryPage.ObjectCount = Convert.ToInt32(DbHelperSQL.GetSingle(count, parameters));
             //获得查询对象集合
             sql = Utils.BuildQueryPageSql("test_student",queryParamList, queryOrderList, queryPage);
             DataSet dataSet = DbHelperSQL.Query(sql, parameters);
             return dataSet;
         }


         /// <summary>
         /// [查询]带分页多条件、多排序查询
         /// </summary>
         /// <param name="queryParamList">查询参数集合</param>
         /// <param name="queryOrderList">排序参数集合</param>
         /// <param name="queryPage">分页</param>
         /// <returns>对象集合</returns>
         public  List<TestStudentEntity> QueryList(List<QueryParam> queryParamList,List<QueryOrder> queryOrderList, QueryPage queryPage)
         {
             string where = " WHERE 1=1";
             string count ="";
             string sql = "";
             MySqlParameter[] parameters;
             //构造查询参数
             parameters = Utils.BuildSqlParameters(queryParamList);
             //构造查询条件
             where = Utils.BuildWhere(queryParamList);
             //查询对象总数
             count = "SELECT COUNT(*) FROM test_student "+ where;
             queryPage.ObjectCount = Convert.ToInt32(DbHelperSQL.GetSingle(count, parameters));
             //获得查询对象集合
             sql = Utils.BuildQueryPageSql("test_student",queryParamList, queryOrderList, queryPage);
             DataSet dataSet = DbHelperSQL.Query(sql, parameters);
             if (dataSet.Tables[0].Rows.Count > 0) 
             {
                 return RowsToList(dataSet.Tables[0].Rows);
             }
             return null;
         }


         /// <summary>
         /// 将一行数据转换为对象
         /// </summary>
         /// <param name="dataRow">一行数据</param>
         /// <returns>一个数据对象</returns>
         private  TestStudentEntity DataRowToEntity(DataRow dataRow)
         {
             TestStudentEntity entity = new TestStudentEntity();
             DataRow row = dataRow;
             if((row["Id"]!=null)&&(row["Id"].ToString() != ""))
             {
                 entity.Id = int.Parse(row["Id"].ToString());
             }
             if((row["Name"]!=null)&&(row["Name"].ToString() != ""))
             {
                 entity.Name = row["Name"].ToString();
             }
             if((row["Age"]!=null)&&(row["Age"].ToString() != ""))
             {
                 entity.Age = int.Parse(row["Age"].ToString());
             }
             if((row["CreateBy"]!=null)&&(row["CreateBy"].ToString() != ""))
             {
                 entity.CreateBy = int.Parse(row["CreateBy"].ToString());
             }
             if((row["CreateTime"]!=null)&&(row["CreateTime"].ToString() != ""))
             {
                 entity.CreateTime = DateTime.Parse(row["CreateTime"].ToString());
             }
             if((row["ModifyBy"]!=null)&&(row["ModifyBy"].ToString() != ""))
             {
                 entity.ModifyBy = int.Parse(row["ModifyBy"].ToString());
             }
             if((row["ModifyTime"]!=null)&&(row["ModifyTime"].ToString() != ""))
             {
                 entity.ModifyTime = DateTime.Parse(row["ModifyTime"].ToString());
             }
             if((row["Remark"]!=null)&&(row["Remark"].ToString() != ""))
             {
                 entity.Remark = row["Remark"].ToString();
             }

             return entity;
         }


         /// <summary>
         /// 将多行数据转化为对象集合
         /// </summary>
         /// <param name="dataRows">多行数据</param>
         /// <returns>对象集合</returns>
         private  List<TestStudentEntity> RowsToList(DataRowCollection dataRows) 
         {
             int count = dataRows.Count;
             List<TestStudentEntity> list = new List<TestStudentEntity>();
             for (int i = 0; i < count; i++)
             {
                 TestStudentEntity entity = DataRowToEntity(dataRows[i]);
                 list.Add(entity);
             }
             return list;
         }
    }
}

